<?php
class hschool_student_course{
	public $info_array = array();
	public $page_array = array();
	public $tree_array = array();
	public static $all_course_array = array();

	static function &instance() {
		static $object;
		if(empty($object)) {
			$object = new self();
		}
		return $object;
	}

	/*
	*	取出一个信息
	*/
	public function get_one($id='') {
		$info = array();
		if($id){
			$sql = "SELECT * FROM ".DB::table('student_course')." WHERE trade_id='".$id."' LIMIT 1";
			$info = DB::fetch_first($sql);	
		}
		return $info;
	}

	/*
	*	取出一个信息 通过 用户编号 user_id 产品类别 trade_type 产品编号 trade_opt_id 
	*/
	public function get_one_param($user_id, $trade_opt_id, $trade_type=1) {
		$info = array();
		if($user_id && $trade_opt_id){
			$sql = "SELECT * FROM ".DB::table('student_course')." WHERE user_id='".$user_id."' AND trade_type='".$trade_type."' AND trade_opt_id='".$trade_opt_id."' LIMIT 1";
			$info = DB::fetch_first($sql);	
		}
		return $info;
	}

	/*
	*	创建
	*/
	public function add() {
		global $_G;
		$is_submit = isset($_REQUEST['is_submit']) ? $_REQUEST['is_submit']:'';
		if(!empty($is_submit)){
			$id = isset($_REQUEST['id']) ? $_REQUEST['id']:'';
		}else{

		}
	}

	/*
	*	编辑
	*/
	public function edit() {
		global $_G;
		$is_submit = isset($_REQUEST['is_submit']) ? $_REQUEST['is_submit']:'';
		if(!empty($is_submit)){

		}else{
			$id = isset($_REQUEST['id']) ? $_REQUEST['id']:'';
		}
	}

	/*
	*	删除
	*/
	public function del() {
		$is_submit = isset($_REQUEST['is_submit']) ? $_REQUEST['is_submit']:'';
		if(!empty($is_submit)){
			$course_ids_array = array();
			$course_ids_array = isset($_REQUEST['trade_ids']) ? $_REQUEST['trade_ids']:array();
			if($course_ids_array){
				$update = array(
					'isdelete'=> 1
				);
				$where = ' id IN ('.dimplode($course_ids_array).') ';
				$effects = DB::update('student_course',$update,$where);
			}
			//DEBUG 接收用户信息 end
			$this->return_json(false,'操作成功');
			echo json_encode($return_array);
		}
	}

	/*
	*	人工退款
	*/
	public function hand_refund() {
		global $_G;
		$is_submit = isset($_REQUEST['is_submit']) ? $_REQUEST['is_submit']:'';
		if(!empty($is_submit) && 1==$_G['user_id']){
			$user_id = isset($_REQUEST['user_id']) ? $_REQUEST['user_id']:'';
			$course_id = isset($_REQUEST['course_id']) ? $_REQUEST['course_id']:'';
			$trade_id = isset($_REQUEST['trade_id']) ? $_REQUEST['trade_id']:'';
            $course_cate = isset($_REQUEST['course_cate']) ? $_REQUEST['course_cate']:'';
			//DEBUG 取出并更新交易记录
			$sql = "SELECT * FROM ".DB::table('trade')." WHERE trade_id='".$trade_id."' LIMIT 1";
			$date_trade = DB::fetch_first($sql);
            if(1==$course_cate){
                $sql_course = "SELECT * FROM ".DB::table('course')." WHERE id='".$course_id."' LIMIT 1";
                $date_course = DB::fetch_first($sql_course);
            }
            if(2==$course_cate){
                $sql_course = "SELECT * FROM ".DB::table('vod')." WHERE id='".$course_id."' LIMIT 1";
                $date_course = DB::fetch_first($sql_course);
            }
			if($date_trade){
				//DEBUG 判断订单关联的课程是否全部退款
				$sql = "SELECT trade_id FROM ".DB::table('student_course')." WHERE trade_id='".$trade_id."' AND (course_flag=1 || course_flag=1) AND course_id <> '".$course_id."' AND course_cate='".$course_cate."' AND group_course_id=0 LIMIT 1";
				$is_exist_refund = DB::result_first($sql);
				if($is_exist_refund){
					$change_trade_status = 'REFUND_SUCCESS';
				}else{
					$change_trade_status = 'REFUND_CLOSED';
				}
				//DEBUG 插入退款 交易记录日志
				$date_trade['trade_status']=$change_trade_status;
				$date_trade['modify_dateline']=TIMESTAMP;
				$date_trade['total_fee']=$date_course['price'];
				$insert_id = DB::insert('trade_log',$date_trade,true);
				if($insert_id){
					//DEBUG 更新订单支付状态
					$data = array('trade_status'=>$change_trade_status,'modify_dateline'=>TIMESTAMP);
					$where = array('trade_id'=>$trade_id);
					$effects = @DB::update('trade',$data,$where);;
				}
			}
            //DEBUG 更新选课支付状态
            $data = array('course_flag'=>0,'course_type'=>0,'trade_id'=>0);
            $where = array('uid'=>$user_id,'course_id'=>$course_id,'course_cate'=>$course_cate);
            $effects = @DB::update('student_course',$data,$where);
            //DEBUG 判断是否是组合课程收款 开始
            if(!empty($date_course['group_course_ids'])){
                $data = array('course_flag'=>0,'course_type'=>0,'trade_id'=>0);
                $where = array('uid'=>$user_id,'group_course_id'=>$course_id,'course_cate'=>$course_cate);
                $effects = @DB::update('student_course',$data,$where);
            }
            //DEBUG 判断是否是组合课程收款 结束
            //DEBUG 取出课程已支付人数 并 更新选课支付人数
            $sql = "SELECT COUNT(*) FROM ".DB::table('student_course')." WHERE course_flag=1 AND course_id='".$course_id."'";
            $course_trade_success_total = DB::result_first($sql);
            @DB::update('course',array('count'=>$course_trade_success_total),array('id'=>$course_id));
            //DEBUG 更新数据库 资料费
            //@DB::delete("user_shipping",array('user_id'=>$user_id));
            self::return_json('false',"操作成功");
		}else{
			self::return_json();
		}
	}

	/*
	*	人工收款
	*/
	public function hand_trade() {
		global $_G;
		$is_submit = isset($_REQUEST['is_submit']) ? $_REQUEST['is_submit']:'';
		if(!empty($is_submit) && 1==$_G['user_id']){
			$user_id = isset($_REQUEST['user_id']) ? $_REQUEST['user_id']:'';
			$course_id = isset($_REQUEST['course_id']) ? $_REQUEST['course_id']:'';
			$trade_id = isset($_REQUEST['trade_id']) ? $_REQUEST['trade_id']:'';
            $course_cate = isset($_REQUEST['course_cate']) ? $_REQUEST['course_cate']:'';
			//DEBUG 取出并更新交易记录
			$sql = "SELECT * FROM ".DB::table('trade')." WHERE trade_id='".$trade_id."' LIMIT 1";
			$date_trade = DB::fetch_first($sql);
            if(1==$course_cate){
                $sql_course = "SELECT * FROM ".DB::table('course')." WHERE id='".$course_id."' LIMIT 1";
            }
            if(2==$course_cate){
                $sql_course = "SELECT * FROM ".DB::table('vod')." WHERE id='".$course_id."' LIMIT 1";
            }
			$date_course = DB::fetch_first($sql_course);
			if(!empty($date_trade)){
				//DEBUG 判断关联选课是否已全部付款
				$sql = "SELECT trade_id FROM ".DB::table('student_course')." WHERE trade_id='".$trade_id."' AND course_flag=0 AND course_id <> '".$course_id."' AND group_course_id=0 LIMIT 1";
				$is_exist_unpay = DB::result_first($sql);
				if($is_exist_unpay){
					$change_trade_status = 'TRADE_PART_SUCCESS';
				}else{
					$change_trade_status = 'TRADE_SUCCESS';
				}
				$date_trade['trade_status']=$change_trade_status;
                if('4' == $date_trade['trade_way']){
                    $date_trade['trade_way']=4;
                }else{
                    $date_trade['trade_way']=2;
                }
				$date_trade['modify_dateline']=TIMESTAMP;
				//DEBUG 更新订单支付状态
				$data = array('trade_status'=>$change_trade_status,'modify_dateline'=>TIMESTAMP,'trade_way'=>$date_trade['trade_way']);
				$where = array('user_id'=>$user_id,'trade_id'=>$trade_id);
                $data['first_trade_success_time']=TIMESTAMP;
				$effects = DB::update('trade',$data,$where);
                if(1==$course_cate){
                    //DEBUG 更新数据库 资料费
                    @DB::insert("user_shipping",array('user_id'=>$user_id,'us_value'=>$_G['setting']['system_shopping_value'],'ccc_id'=>$date_trade['course_cate_custom']));   
                }
			}
			$date_trade['total_fee']=$date_course['price'];
            $date_trade['first_trade_success_time']=TIMESTAMP;
			$insert_id = DB::insert('trade_log',$date_trade,true);
			if($insert_id){
				//DEBUG 更新选课支付状态
				$data = array('course_flag'=>1,'course_type'=>0);
				$where = array('uid'=>$user_id,'course_id'=>$course_id);
				$effects = @DB::update('student_course',$data,$where);
                //DEBUG 判断是否是组合课程收款 开始
                if(!empty($date_course['group_course_ids'])){
                    $data = array('course_flag'=>1,'course_type'=>0);
                    $where = array('uid'=>$user_id,'group_course_id'=>$course_id);
                    $effects = @DB::update('student_course',$data,$where);
                }
                //DEBUG 判断是否是组合课程收款 结束
				//DEBUG 取出课程已支付人数 并 更新选课支付人数
				$sql = "SELECT COUNT(*) FROM ".DB::table('student_course')." WHERE course_flag=1 AND course_id='".$course_id."' AND course_cate='".$course_cate."'";
				$course_trade_success_total = DB::result_first($sql);
				//DEBUG 由于 trade_type 默认 = 1
                if(1==$course_cate){
                    @DB::update('course',array('count'=>$course_trade_success_total),array('id'=>$course_id));
                    //DEBUG 发送短息通知给购买人
                    $course_name = @DB::result_first("SELECT name FROM ".DB::table('course')." WHERE id='".$course_id."' LIMIT 1");
                }
                if(2==$course_cate){
                    @DB::update('vod',array('count'=>$course_trade_success_total),array('id'=>$course_id));
                    //DEBUG 发送短息通知给购买人
                    $course_name = @DB::result_first("SELECT name FROM ".DB::table('vod')." WHERE id='".$course_id."' LIMIT 1");
                }
                $course_number = 1;
                $user_info = @DB::fetch_first("SELECT user_realname, user_phone FROM ".DB::table('users')." WHERE user_id='".$user_id."' LIMIT 1");
                $mobile = $user_info['user_phone'];
                $realname = $user_info['user_realname'];
                if($mobile && $realname && $course_name && $course_number){
                    $target = $_G['setting']['system_sms_api'];
                    $system_sms_account = str_replace('###mobile###',$mobile,$_G['setting']['system_sms_account']);
                    $sign_array = array('###realname###','###course_name###','###course_number###');
                    $sign_replace_array = array($realname,$course_name,$course_number);
                    $sms_content = str_replace($sign_array,$sign_replace_array,$_G['setting']['system_sms_content']);
                    $post_data = $system_sms_account.rawurlencode($sms_content);
                    //密码可以使用明文密码或使用32位MD5加密
                    $return_curl = dzf_curl_post($post_data, $target);
                    //@file_put_contents(SITE_ROOT.'./data/logs/alipay/alipay_bank_curl2.txt',json_encode($return_curl));
                }
				self::return_json('false',"操作成功");
			}
		}else{
			self::return_json();
		}
	}
	/*
	*	查看汇款单
	*/
    public function trade_bill() {
        global $_G;
        $user_id = isset($_REQUEST['user_id']) ? $_REQUEST['user_id'] : '';
        $trade_id = isset($_REQUEST['trade_id']) ? $_REQUEST['trade_id'] : '';
        if($user_id && $trade_id){
           //DEBUG 获取汇款单信息
            $sql = "SELECT * FROM ".DB::table('trade_bill')." WHERE tb_trade_id='".$trade_id."' AND tb_user_id='".$user_id."'";
            $result = DB::fetch_all($sql);
        }
        include template('admin/hschool/student_course_trade_bill');
    }
    
	/*
	*	当前动作的首页
	*/
	public function index($pos='admincp') {
		global $_G;
		$page_array = array();
		$sqlcount = $sql = $wheresql = $title_keyword = $pageNum = $numPerPage = '';
		$pageNum = isset($_REQUEST['pageNum']) ? $_REQUEST['pageNum'] : 1;
		$numPerPage = isset($_REQUEST['numPerPage']) ? $_REQUEST['numPerPage'] : 10;
		$pagestart = ($pageNum - 1) * $numPerPage;

		$out_trade_no = isset($_REQUEST['out_trade_no']) ? $_REQUEST['out_trade_no'] : '';
		$user_id = isset($_REQUEST['userInfo_user_id']) ? $_REQUEST['userInfo_user_id'] : '';
		$user_name = isset($_REQUEST['userInfo_user_name']) ? $_REQUEST['userInfo_user_name'] : '';
		$user_realname = isset($_REQUEST['userInfo_user_realname']) ? $_REQUEST['userInfo_user_realname'] : '';
		$user_phone = isset($_REQUEST['userInfo_user_phone']) ? $_REQUEST['userInfo_user_phone'] : '';
		$course_flag = isset($_REQUEST['course_flag']) ? $_REQUEST['course_flag'] : '';
        $trade_way = isset($_REQUEST['trade_way']) ? $_REQUEST['trade_way'] : '';
		//DEBUG 增加排序字段 开始
		$order_by = isset($_REQUEST['order_by']) && !empty($_REQUEST['order_by']) ? $_REQUEST['order_by'] : 'sc.id';
        $course_cate_custom = isset($_REQUEST['course_cate_custom']) ? $_REQUEST['course_cate_custom'] : '';
		//DEBUG 增加排序字段 结束
		//$wheresql = " WHERE t.trade_type=1 ";//默认取直播订单 //此处WHERE 没有加 后期集成 直播支付的时候需要考虑如何整合
		$wheresql = " WHERE group_course_id=0  AND sc.trade_id > 0 ";
		if($out_trade_no){
			$wheresql .= " AND t.out_trade_no = '".$out_trade_no."' ";
		}
		if($user_id){
			$wheresql .= " AND u.user_id = '".$user_id."' ";
		}
		if($user_name){
			$wheresql .= " AND u.user_name = '".$user_name."' ";
		}
		if($user_phone){
			$wheresql .= " AND u.user_phone = '".$user_phone."' ";
		}
		if($user_realname){
			$wheresql .= " AND u.user_realname LIKE '%".$user_realname."%' ";
		}
		if($course_flag){
			//1 未付款
			if(1==$course_flag){
				$wheresql .= " AND sc.course_flag = '0' AND sc.course_type='0'";
			}
			//2 已付款
			if(2==$course_flag){
				$wheresql .= " AND sc.course_flag = '1' ";
				$add_wheresql = " AND tl.trade_status='TRADE_SUCCESS' ";
				$add_field = "tl.create_dateline AS tl_create_dateline, ";
				$add_sql = " LEFT JOIN ".DB::table('trade_log')." AS tl ON t.trade_id=tl.trade_id ";                
			}
			//3 已退款
			if(3==$course_flag){
				$wheresql .= " AND sc.course_flag = '0' AND sc.course_type='2' ";
			}
			//4 申请退款
			if(4==$course_flag){
				$wheresql .= " AND sc.course_flag = '1' AND sc.course_type='1' ";
			}
			//5 免费学员 
			if(5==$course_flag){
				$wheresql .= " AND sc.course_flag = '2' ";
			}
		}
		if($trade_way){
			$wheresql .= " AND t.trade_way = '".$trade_way."' ";
		}
		if('tl.create_dateline'==$order_by && empty($add_field)){
			$order_by='sc.id';
		}
        //DEBUG 增加退款管理数据页面独立 显示 申请退款 和 已退款 的选课数据 开始
        $refund = isset($_REQUEST['refund']) ? $_REQUEST['refund'] : '';
        if($refund){
			//DEBUG 条件 已退款 和 申请退款
			$wheresql .= " AND ((sc.course_flag = '0' AND sc.course_type='2') || ( sc.course_flag = '1' AND sc.course_type='1' ))";
        }
        //DEBUG 增加退款管理数据页面独立 显示 申请退款 和 已退款 的选课数据 结束
		$sqlcount = "SELECT count(*) FROM ".DB::table('student_course')." AS sc LEFT JOIN ".DB::table('users')." AS u ON sc.uid=u.user_id LEFT JOIN ".DB::table('trade')." AS t ON sc.trade_id=t.trade_id ".$wheresql;
		$pagetotal = DB::result_first($sqlcount);
		$sql = "SELECT sc.*,u.*,t.trade_id,t.out_trade_no,t.trade_status, ".$add_field." t.trade_way FROM ".DB::table('student_course')." AS sc LEFT JOIN ".DB::table('users')." AS u ON sc.uid=u.user_id LEFT JOIN ".DB::table('trade')." AS t ON sc.trade_id=t.trade_id ".$add_sql.$wheresql.$add_wheresql." ORDER BY ".$order_by." DESC ".DB::limit($pagestart, $numPerPage);
		$page_array = DB::fetch_all($sql);
		$class_trade_path = libfile('hschool/trade', 'class/admin','..');
		if(file_exists($class_trade_path)){
			require_once $class_trade_path;
			$hschool_trade = new hschool_trade();
		}
		foreach($page_array AS $key => $value){
			if($hschool_trade){
				$page_array[$key]['trade_status_name'] = $hschool_trade->get_trade_status_name($value['trade_status']);
			}
            $tmp_course_info = array();
            if($value['course_id'] && 1==$value['course_cate']){
                $tmp_course_info = DB::fetch_first('SELECT name,price FROM '.DB::table('course')." WHERE id='".$value['course_id']."' LIMIT 1");
                $page_array[$key]['name']='[巩固班]'.$tmp_course_info['name'];
                $page_array[$key]['price']=$tmp_course_info['price'];
            }
            if($value['course_id'] && 2==$value['course_cate']){
                $tmp_course_info = DB::fetch_first('SELECT name,price FROM '.DB::table('vod')." WHERE id='".$value['course_id']."' LIMIT 1");
                $page_array[$key]['name']='[点播]'.$tmp_course_info['name'];
                $page_array[$key]['price']=$tmp_course_info['price'];
            }
		}
        //DEBUG 后台增加不同模板处理 与 分页处理 $pos ="front" 是前台 默认 $pos="admincp" 是后台
        if('front'==$pos){
            //总页数
            $realpages = @ceil($pagetotal / $numPerPage);
            if($realpages <= 0){
                $realpages=1;
            }
            //前一页
            $page_pre = max(1, intval(($pageNum-1)));
            //后一页
            $page_next = min(intval(($pageNum+1)), $realpages);
            //DEBUG 加载模板
            $do=isset($_REQUEST['do']) && !empty($_REQUEST['do']) ? $_REQUEST['do']:'';
            include template('trade/index');
        }else{
            //总页数
            $realpages = @ceil($pagetotal / $numPerPage);
            if($realpages <= 0){
                $realpages=1;
            }
            //前一页
            $page_pre = max(1, intval(($pageNum-1)));
            //后一页
            $page_next = min(intval(($pageNum+1)), $realpages);
            //DEBUG 增加模板
            include template('admin/hschool/student_course');
        }
	}
    
    /*
	*	导出当前动作的数据
	*/
	public function index_export($pos='admincp') {
		global $_G;
        //ini_set('display_errors','On');
        //error_reporting(E_ALL);
        ini_set('memory_limit', '1024M');
		$page_array = array();
		$sqlcount = $sql = $wheresql = $title_keyword = $pageNum = $numPerPage = '';
		$pageNum = isset($_REQUEST['pageNum']) ? $_REQUEST['pageNum'] : 1;
		$numPerPage = isset($_REQUEST['numPerPage']) ? $_REQUEST['numPerPage'] : 10;
		$pagestart = ($pageNum - 1) * $numPerPage;
		$out_trade_no = isset($_REQUEST['out_trade_no']) ? $_REQUEST['out_trade_no'] : '';
		$user_id = isset($_REQUEST['userInfo_user_id']) ? $_REQUEST['userInfo_user_id'] : '';
		$user_name = isset($_REQUEST['userInfo_user_name']) ? $_REQUEST['userInfo_user_name'] : '';
		$user_realname = isset($_REQUEST['userInfo_user_realname']) ? $_REQUEST['userInfo_user_realname'] : '';
		$user_phone = isset($_REQUEST['userInfo_user_phone']) ? $_REQUEST['userInfo_user_phone'] : '';
		$course_flag = isset($_REQUEST['course_flag']) ? $_REQUEST['course_flag'] : '';
        $trade_way = isset($_REQUEST['trade_way']) ? $_REQUEST['trade_way'] : '';
        $course_cate_custom = isset($_REQUEST['course_cate_custom']) ? $_REQUEST['course_cate_custom'] : '';
		//DEBUG 增加排序字段 开始
		$order_by = isset($_REQUEST['order_by']) && !empty($_REQUEST['order_by']) ? $_REQUEST['order_by'] : 'sc.id';
        $course_cate_custom = isset($_REQUEST['course_cate_custom']) ? $_REQUEST['course_cate_custom'] : '';
		//DEBUG 增加排序字段 结束
		//$wheresql = " WHERE t.trade_type=1 ";//默认取直播订单 //此处WHERE 没有加 后期集成 直播支付的时候需要考虑如何整合
		if($out_trade_no){
			$wheresql .= " AND t.out_trade_no = '".$out_trade_no."' ";
		}
		if($user_id){
			$wheresql .= " AND u.user_id = '".$user_id."' ";
		}
		if($user_name){
			$wheresql .= " AND u.user_name = '".$user_name."' ";
		}
		if($user_phone){
			$wheresql .= " AND u.user_phone = '".$user_phone."' ";
		}
		if($user_realname){
			$wheresql .= " AND u.user_realname LIKE '%".$user_realname."%' ";
		}
        if($course_cate_custom){
			$wheresql .= " AND cc.course_cate_custom = '".$course_cate_custom."' ";
		}
		if($course_flag){
			//1 未付款
			if(1==$course_flag){
				$wheresql .= " AND sc.course_flag = '0' AND sc.course_type='0'";
			}
			//2 已付款
			if(2==$course_flag){
				$wheresql .= " AND sc.course_flag = '1' ";
				$add_wheresql = " AND tl.trade_status='TRADE_SUCCESS' ";
				$add_field = "tl.create_dateline AS tl_create_dateline, ";
				$add_sql = " LEFT JOIN ".DB::table('trade_log')." AS tl ON t.trade_id=tl.trade_id ";                
			}
			//3 已退款
			if(3==$course_flag){
				$wheresql .= " AND sc.course_flag = '0' AND sc.course_type='2' ";
			}
			//4 申请退款
			if(4==$course_flag){
				$wheresql .= " AND sc.course_flag = '1' AND sc.course_type='1' ";
			}
			//5 免费学员 
			if(5==$course_flag){
				$wheresql .= " AND sc.course_flag = '2' ";
			}
		}
		if($trade_way){
			$wheresql .= " AND t.trade_way = '".$trade_way."' ";
		}
		if('tl.create_dateline'==$order_by && empty($add_field)){
			$order_by='sc.id';
		}
        //DEBUG 增加退款管理数据页面独立 显示 申请退款 和 已退款 的选课数据 开始
        $refund = isset($_REQUEST['refund']) ? $_REQUEST['refund'] : '';
        if($refund){
			//DEBUG 条件 已退款 和 申请退款
			$wheresql .= " AND ((sc.course_flag = '0' AND sc.course_type='2') || ( sc.course_flag = '1' AND sc.course_type='1' ))";
        }
        //DEBUG 增加退款管理数据页面独立 显示 申请退款 和 已退款 的选课数据 结束
		//$sqlcount = "SELECT count(*) FROM ".DB::table('student_course')." AS sc LEFT JOIN ".DB::table('users')." AS u ON sc.uid=u.user_id LEFT JOIN ".DB::table('trade')." AS t ON sc.trade_id=t.trade_id ".$wheresql;
		//$pagetotal = DB::result_first($sqlcount);
        if(1){
            $sql = "SELECT sc.id,dd.d_name AS d_name_area,d.d_name AS d_name_dept,u.user_name,u.user_realname,c.class_name,cc.name,u.user_phone,t.trade_id,t.out_trade_no,t.trade_no,t.total_fee,t.trade_status, ".$add_field." t.trade_way,t.first_trade_success_time,cc.price,sc.course_cate
                FROM ".DB::table('student_course')." AS sc 
                LEFT JOIN ".DB::table('users')." AS u ON sc.uid=u.user_id 
                LEFT JOIN ".DB::table('trade')." AS t ON sc.trade_id=t.trade_id
                LEFT JOIN ".DB::table('course')." AS cc ON sc.course_id=cc.id
                LEFT JOIN ".DB::table('class')." AS c ON u.class_id=c.class_id
                LEFT JOIN ".DB::table('dictionary')." AS d ON u.dept_id=d.d_id 
                LEFT JOIN ".DB::table('dictionary')." AS dd ON d.d_parent_id=dd.d_id 
                ".$add_sql." WHERE sc.course_flag=1 AND sc.course_cate=1 AND u.user_role_id=21 
                ".$wheresql.$add_wheresql." ORDER BY ".$order_by." DESC";
            $page_array = DB::fetch_all($sql);
        }
        if(1){
            $sql = "SELECT sc.id,dd.d_name AS d_name_area,d.d_name AS d_name_dept,u.user_name,u.user_realname,c.class_name,cc.name,u.user_phone,t.trade_id,t.out_trade_no,t.trade_no,t.total_fee,t.trade_status, ".$add_field." t.trade_way,t.first_trade_success_time,cc.price,cc.course_cate_custom,sc.course_cate
                FROM ".DB::table('student_course')." AS sc 
                LEFT JOIN ".DB::table('users')." AS u ON sc.uid=u.user_id 
                LEFT JOIN ".DB::table('trade')." AS t ON sc.trade_id=t.trade_id
                LEFT JOIN ".DB::table('vod')." AS cc ON sc.course_id=cc.id
                LEFT JOIN ".DB::table('class')." AS c ON u.class_id=c.class_id
                LEFT JOIN ".DB::table('dictionary')." AS d ON u.dept_id=d.d_id 
                LEFT JOIN ".DB::table('dictionary')." AS dd ON d.d_parent_id=dd.d_id 
                ".$add_sql." 
                WHERE sc.course_flag=1 AND sc.course_cate=2 AND u.user_role_id=21 
                ".$wheresql.$add_wheresql." ORDER BY ".$order_by." DESC";
            $page_array2 = DB::fetch_all($sql);
        }
        foreach($page_array2 AS $key => $value){
            $page_array[] = $value;
        }
		$class_trade_path = libfile('hschool/trade', 'class/admin','..');
		if(file_exists($class_trade_path)){
			require_once $class_trade_path;
			$hschool_trade = new hschool_trade();
		}
		foreach($page_array AS $key => $value){
            $stu_data[$key][0]=$value['id'];
            $stu_data[$key][1]=$value['d_name_area'];
            $stu_data[$key][2]=$value['d_name_dept'];
            $stu_data[$key][3]="'".$value['user_name'];
            $stu_data[$key][4]=$value['user_realname'];
            $stu_data[$key][5]=$value['class_name'];
            $stu_data[$key][6]=$value['user_phone'];
            //判断 巩固班 点播 预习课 的名称 开始
            if(1==$value['course_cate']){
                $stu_data[$key][7]='[巩固班]'.$value['name'];
            }
            if(2==$value['course_cate']){
                if(1==$value['course_cate_custom']){
                    $stu_data[$key][7]='[点播]'.$value['name'];
                }elseif(2==$value['course_cate_custom']){
                    $stu_data[$key][7]='[预习课]'.$value['name'];
                }else{
                    $stu_data[$key][7]='[点播]'.$value['name'];
                }
            }
            //判断 巩固班 点播 预习课 的名称 结束
            $stu_data[$key][8]=$value['price'];
            $stu_data[$key][9]="'".$value['out_trade_no'];
            switch($value['trade_way']){
                case 1:
                    $stu_data[$key][10]='支付宝';
                    break;
                case 2:
                    $stu_data[$key][10]='人工收款';
                    break;
                case 3:
                    $stu_data[$key][10]='支付宝网银支付';
                    break;
                case 4:
                    $stu_data[$key][10]='人工汇款';
                    break;
                case 5:
                    $stu_data[$key][10]='微信支付';
                    break;
            }
            $stu_data[$key][11]="'".$value['trade_no'];
            $stu_data[$key][12]=$value['total_fee'];
			if($hschool_trade){
				$stu_data[$key][13] = $hschool_trade->get_trade_status_name($value['trade_status']);
			}
            if($value['first_trade_success_time']){
                $stu_data[$key][14]=''.date('Y-m-d H:i:s',$value['first_trade_success_time']);
            }else{
                $stu_data[$key][14]='';
            }
		}
		require SITE_ROOT.'/source/lib/excel/class_excel.php';
		$excel_file_name = '已付款记录';//文件名
		$excel_sheet_title = '人员名单';//数据标签页名
		$excel_data = array(
			0 => array('A1'=>'选课编号', 'B1'=>'地区', 'C1'=>'学校','D1'=>'用户名（账号）','E1'=>'真实姓名','F1'=>'所在班级','G1'=>'联系电话','H1'=>'课程名称','I1'=>'课程价格','J1'=>'网站订单编号','K1'=>'支付方式','L1'=>'支付订单编号','M1'=>'订单总价','N1'=>'支付状态','O1'=>'付款时间'),	
			);
		//print_r($stu_data);exit;
		$i=1;
		foreach ( $stu_data as $key=>$value)
		{
			$cos = $key+1;
			$cos_ = $key+2;
			$excel_data[$cos] = array('A'.$cos_=>$value[0],'B'.$cos_=>$value[1],'C'.$cos_=>$value[2],'D'.$cos_=>$value[3],'E'.$cos_=>$value[4],'F'.$cos_=>$value[5],'G'.$cos_=>$value[6],'H'.$cos_=>$value[7],'I'.$cos_=>$value[8],'J'.$cos_=>$value[9],'K'.$cos_=>$value[10],'L'.$cos_=>$value[11],'M'.$cos_=>$value[12],'N'.$cos_=>$value[13],'O'.$cos_=>$value[14]);
			//if($key > 15000) break;
		}
		
		//DEBUG 类函数调用示例
		//DEBUG 类函数调用示例
		//require SITE_ROOT.'./source/lib/excel/class_excel.php';
		//$excel = new excel($excel_file_name, $excel_sheet_title, $excel_data);
		//$excel->output_excel();
			$excel = new excel($excel_file_name, $excel_sheet_title, $excel_data,'Excel5',1,'',2);
			$excel->output_excel();
		/*
        $i=0;
        $csv_data[$i] = array(0=>"选课编号",1=>"地区",2=>"学校",3=>"用户名（账号）",4=>"真实姓名",5=>"所在班级",6=>"联系电话",7=>"课程名称",8=>"课程价格",9=>"网站订单编号",10=>"支付方式",11=>"支付订单编号",12=>"订单总价",13=>"支付状态");
        foreach($stu_data AS $k => $v){
            $i++;
            $csv_data[$i] = array(0=>$v[0],1=>$v[1],2=>$v[2],3=>$v[3],4=>$v[4],5=>$v[5],6=>$v[6],7=>$v[7],8=>$v[8],9=>$v[9],10=>$v[10],11=>$v[11],12=>$v[12],13=>$v[13]);
        }
        $csv_name = date('YmdHis',TIMESTAMP).'.csv';
        $csv = new csv($csv_name,$csv_data);
        $csv->csv_to_httpd();
        */
	}

	/*
	* ajax 返回信息
	*/
	public function return_json($error='true',$message="操作失败或无权限操作") {
		$return_array = array(
			"error" => $error,
			"message" => $message
		);
		echo json_ext($return_array);
		die;
	}
}
?>